package com.zimi.util;

import com.zimi.bean.Column;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.sql.*;
import java.util.*;

/**
 * @author Administrator
 */
public class DbUtil {
    private static final Log logger = LogFactory.getLog(DbUtil.class);

    public static List getCatalogs(Connection c) throws SQLException {
        DatabaseMetaData dmd = c.getMetaData();
        ResultSet rs = null;
        try {
            rs = dmd.getCatalogs();
            List l = new LinkedList();
            while (rs.next()) {
                l.add(rs.getString(1));
            }
            return l;
        } finally {
            if (rs != null) {
                rs.close();
            }
        }
    }

    public static Map getSchemas(Connection c) throws SQLException {
        DatabaseMetaData dmd = c.getMetaData();
        ResultSet rs = null;
        try {
            rs = dmd.getSchemas();
            Map map = new HashMap();
            List l;
            while (rs.next()) {
                String schema = rs.getString(1);
                String catalog = null;
                if (rs.getMetaData().getColumnCount() > 1) {
                    catalog = rs.getString(2);
                }
                ;
                l = (List) map.get(catalog);
                if (l == null) {
                    l = new LinkedList();
                    map.put(catalog, l);
                }
                l.add(schema);
            }
            return map;
        } finally {
            if (rs != null) {
                rs.close();
            }
        }
    }

    public static List getTables(Connection c, String catalog, String schema, String tablePattern) throws SQLException {
        logger.debug("catalog='" + catalog + "'");
        logger.debug("schema='" + schema + "'");
        logger.debug("table='" + tablePattern + "'");
        DatabaseMetaData dmd = c.getMetaData();
        ResultSet rs = null;
        try {
            rs = dmd.getTables(catalog, schema, tablePattern, new String[]{"TABLE", "VIEW", "SYNONYM", "ALIAS"});
            List l = new LinkedList();
            while (rs.next()) {
                l.add(rs.getString(3));
            }
            return l;
        } finally {
            if (rs != null) {
                rs.close();
            }
        }
    }

    public static Set getForeignKeyColumns(Connection c, String catalog, String schema, String table) throws SQLException {
        logger.debug("catalog='" + catalog + "'");
        logger.debug("schema='" + schema + "'");
        logger.debug("table='" + table + "'");
        DatabaseMetaData dmd = c.getMetaData();
        ResultSet rs = null;
        try {
            rs = dmd.getImportedKeys(catalog, schema, table);
            HashSet columns = new HashSet();
            while (rs.next()) {
                columns.add(rs.getString(8));
            }
            return columns;
        } finally {
            if (rs != null) {
                rs.close();
            }
        }
    }

    public static List getPrimaryKeyColumns(Connection c, String catalog, String schema, String table) throws SQLException {
        logger.debug("catalog='" + catalog + "'");
        logger.debug("schema='" + schema + "'");
        logger.debug("table='" + table + "'");
        DatabaseMetaData dmd = c.getMetaData();
        ResultSet rs = null;
        try {
            rs = dmd.getPrimaryKeys(catalog, schema, table);

            List pkColumns = new LinkedList();
            ;
            while (rs.next()) {
                List tmp = getTableColumns(c, catalog, schema, table, rs.getString(4));
                Column pkColumn = (Column) tmp.get(0);
                pkColumns.add(pkColumn);
            }
            return pkColumns;
        } finally {
            if (rs != null) {
                rs.close();
            }
        }
    }

    public static List getTableColumns(Connection c, String catalog, String schema, String table) throws SQLException {
        return getTableColumns(c, catalog, schema, table, null);
    }

    public static List getTableColumns(Connection c, String catalog, String schema, String table, String columnPattern) throws SQLException {
        logger.debug("catalog='" + catalog + "'");
        logger.debug("schema='" + schema + "'");
        logger.debug("table='" + table + "'");
        logger.debug("column='" + columnPattern + "'");
        DatabaseMetaData dmd = c.getMetaData();
        ResultSet rs = null;
        try {
            rs = dmd.getColumns(catalog, schema, table, columnPattern);
            List columns = new LinkedList();
            while (rs.next()) {

                Column aCol = new Column();

                aCol.sqlTypeName = rs.getString("TYPE_NAME");
                aCol.defalutValue = rs.getString(13);
                aCol.name = rs.getString("COLUMN_NAME");
                aCol.sqlType = rs.getShort(5);
                aCol.type = rs.getString("TYPE_NAME");
                aCol.sqlColumnLength = rs.getInt("COLUMN_SIZE");
                aCol.sqlDecimalLength = rs.getInt(9);
                aCol.remark = rs.getString("REMARKS");
                aCol.sqlNotNull = ("NO".equals(rs.getString(18)));

                aCol.javaType = getJavaType(aCol.sqlType, aCol.sqlColumnLength, aCol.sqlDecimalLength);
                // System.out.println("111111111111111111111"+aCol.type);
                columns.add(aCol);
            }
            return columns;
        } finally {
            if (rs != null) {
                rs.close();
            }
        }

    }

    public static String getJavaType(int sqlType, int columnSize, int decimalDigits) {
        logger.debug("sqlType=" + sqlType);
        logger.debug("columnSize=" + columnSize);
        logger.debug("decimalDigits=" + decimalDigits);
        String rv = "String";
        if (sqlType == Types.CHAR || sqlType == Types.VARCHAR) {
            rv = "String";
        } else if (sqlType == Types.FLOAT || sqlType == Types.REAL) {
            rv = "Float";
        } else if (sqlType == Types.INTEGER) {
            rv = "Integer";
        } else if (sqlType == Types.DOUBLE) {
            rv = "Double";
        } else if (sqlType == Types.DATE) {
            rv = "Date";
            // rv = String.class;
        } else if (sqlType == Types.TIMESTAMP) {
            // rv = java.util.Date.class;
            rv = "Date";
        } else if (sqlType == Types.TIME) {
            // rv = java.util.Date.class;
            rv = "Date";
        }
        // commented to support JDK version < 1.4
        /*
         * else if (sqlType == Types.BOOLEAN) { rv = Boolean.class; }
         */
        else if (sqlType == Types.SMALLINT) {
            rv = "Short";
        } else if (sqlType == Types.BIT) {
            rv = "Byte";
            // rv = "Integer";
        } else if (sqlType == Types.BIGINT) {
            rv = "Long";
        } else if (sqlType == Types.BLOB || sqlType == Types.BLOB) {
            rv = "byte[]";
        } else if (sqlType == Types.NUMERIC || sqlType == Types.DECIMAL) {
            if (decimalDigits == 0) {
                if (columnSize == 1) {
                    // rv = Byte.class;
                    rv = "Integer";
                } else if (columnSize < 5) {
                    rv = "Short";
                } else if (columnSize < 10) {
                    rv = "Integer";
                } else {
                    rv = "Long";
                }
            } else {
                if (columnSize < 9) {
                    rv = "Float";
                } else {
                    rv = "Double";
                }
            }
        }
        return rv;
    }

}
